package com.example.test.mapper;

import com.example.test.bean.Customers;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.test.bean.DTO.output.CustomersListOutputDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 客户信息表 Mapper 接口
 * </p>
 *
 * @author dz
 * @since 2022-04-10
 */
@Mapper
public interface CustomersMapper extends BaseMapper<Customers> {
    //根据keyword模糊查询
    @Select("SELECT\n" +
            "\tcustomer_id,\n" +
            "\tcustomer_name,\n" +
            "\tphone,\n" +
            "\twechat,\n" +
            "\tqq,\n" +
            "\temail,\n" +
            "\tadress,\n" +
            "\tamount_purchase,\n" +
            "\tfirm\n" +
            "FROM\n" +
            "\tcustomers\n" +
            "WHERE\n" +
            "\tcustomer_name like concat('%',#{keyword},'%')\n" +
            "AND\n" +
            "\tdeleted = 0\n" +
            "LIMIT #{size} OFFSET #{offset}\n")
    List<CustomersListOutputDTO> getCustomByKeyword(String keyword, Long size, Long offset);

    //根据id查询
    @Select("SELECT\n" +
            "\tcustomer_id,\n" +
            "\tcustomer_name,\n" +
            "\tphone,\n" +
            "\twechat,\n" +
            "\tqq,\n" +
            "\temail,\n" +
            "\tadress,\n" +
            "\tamount_purchase,\n" +
            "\tfirm\n" +
            "FROM\n" +
            "\tcustomers\n" +
            "WHERE\n" +
            "\tcustomer_id = #{customer_id}\n" +
            "AND\n" +
            "\tdeleted = 0\n")
    Customers getCustomById(Long customerId);

    //根据name搜索customer
    @Select("SELECT\n" +
            "\tcustomer_id,\n" +
            "\tcustomer_name,\n" +
            "\tphone,\n" +
            "\twechat,\n" +
            "\tqq,\n" +
            "\temail,\n" +
            "\tadress,\n" +
            "\tamount_purchase,\n" +
            "\tfirm\n" +
            "FROM\n" +
            "\tcustomers\n" +
            "WHERE\n" +
            "\tcustomer_name = #{customerName}\n" +
            "AND\n" +
            "\tdeleted = 0\n")
    Customers getCustomByName(String customerName);

    //根据name搜索id
    @Select("SELECT\n" +
            "\tcustomer_id\n" +
            "FROM\n" +
            "\tcustomers\n" +
            "WHERE\n" +
            "\tcustomer_name = #{customerName}\n" +
            "AND\n" +
            "\tdeleted = 0\n")
    Long getCustIdByName(String customerName);
}
